package dao.impl;

import dao.UserDao;
import model.Role;
import model.User;
import util.DBUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl extends DBUtils implements UserDao {

    @Override
    public int addUser(User user) {
        String sql = "insert into user(loginname, password, realname, sex, email, address, phone, cardid, info, roleid) values (?,?,?,?,?,?,?,?,?,?)";
        return super.update(sql, user.getLoginName(), user.getPassWord(), user.getRealName(), user.getSex(), user.getEmail(), user.getAddress(), user.getPhone(), user.getCardId(), user.getInfo(), user.getRoleId());
    }

    @Override
    public int deleteUser(int id) {
        String sql = "delete from user where userid=?";
        return super.update(sql, id);
    }

    @Override
    public int updateUser(User user) {
        String sql = "update user set loginname=?,password=?,realname=?,sex=?,email=?,address=?,phone=?,cardid=?,info=?,roleid=? where userid=?";
        return super.update(sql, user.getLoginName(), user.getPassWord(), user.getRealName(), user.getSex(), user.getEmail(), user.getAddress(), user.getPhone(), user.getCardId(), user.getInfo(), user.getRoleId(), user.getUserId());
    }

    @Override
    public User login(String username, String password) {
        String sql = "select * from user where loginname=? and password=?";
        resultSet = super.query(sql, username, password);
        User user = null;
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    user = new User();
                    user.setUserId(Integer.parseInt(resultSet.getString("userid")));
                    user.setLoginName(resultSet.getString("loginname"));
                    user.setPassWord(resultSet.getString("password"));
                    user.setRealName(resultSet.getString("realname"));
                    user.setRoleId(resultSet.getInt("roleid"));
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                closeAll();
            }
        }
        return user;
    }

    @Override
    public User queryUser(int id) {
        String sql = "select * from user where userid=?";
        ResultSet rs = super.query(sql, id);
        User user = null;
        if (rs != null) {
            user = new User();
            //查到了
            try {
                while (rs.next()) {
                    user.setUserId(Integer.parseInt(rs.getString("userid")));
                    user.setLoginName(rs.getString("loginname"));
                    user.setPassWord(rs.getString("password"));
                    user.setRealName(rs.getString("realname"));
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return user;
    }

    @Override
    public List<User> getUsers(int pageIndex, int pageSize) {
        String sql = "select u.userid, u.loginname,u.realname,r.rolename from user u,role r where u.roleid=r.roleid limit ?,?";
        List<User> userList = new ArrayList<>();
        User user = null;
        Role role = null;
        resultSet = query(sql, (pageIndex - 1) * pageSize, pageSize);
        try {
            while (resultSet.next()) {
                user = new User();
                user.setUserId(resultSet.getInt("userid"));
                user.setLoginName(resultSet.getString("loginname"));
                user.setRealName(resultSet.getString("realname"));
                role = new Role();
                role.setRoleName(resultSet.getString("rolename"));
                user.setRole(role);
                userList.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return userList;
    }

    @Override
    public User getUserById(int userid) {
        String sql = "select * from user u,role r where u.roleid=r.roleid and userid=?";
        User user = null;
        Role role = null;
        try {
            resultSet = query(sql, userid);
            if (resultSet.next()) {
                user = new User(resultSet.getInt("userid")
                        , resultSet.getString("loginname")
                        , resultSet.getString("password")
                        , resultSet.getString("realname")
                        , resultSet.getInt("sex")
                        , resultSet.getString("email")
                        , resultSet.getString("address")
                        , resultSet.getString("phone")
                        , resultSet.getString("cardid")
                        , resultSet.getString("info")
                        , resultSet.getInt("roleid"));
                role = new Role();
                role.setRoleId(resultSet.getInt("roleid"));
                role.setRoleName(resultSet.getString("rolename"));
                role.setRoleState(resultSet.getInt("rolestate"));
                user.setRole(role);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return user;
    }

    @Override
    public int total() {
        String sql = "select COUNT(1) from user u,role r where u.roleid=r.roleid";
        int total = 0;
        resultSet = query(sql);
        try {
            while (resultSet.next()) {
                total = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }
}
